﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Text.RegularExpressions;
using System.Data;
using System.Data.Common;
using System.Globalization;
using T4Ui.Utility;

namespace T4Ui.dal
{
    public class SqlServiceDbBase: DbBase
    {



        public SqlServiceDbBase() :
            base()
        {

        }
          //string FKSql = @"select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name ={0}table_name";


          //string TABLE_SQL = @"select * from user_tables ";

          //string COLUMN_SQL = @"select Table_Name , COLUMN_NAME ColumnName ,DATA_TYPE DataType, DATA_LENGTH MaxLength  from user_tab_columns where Table_Name={0}Table_Name";

        //string PriSql = "";

        public override string GetTableSql()
        {
            return @"SELECT *
    FROM  INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE='BASE TABLE' ";
        }

        public override DbCommand GetColumnCmd(Table tbl)
        {
            DbCommand cmd = dal.GetSqlStringCommond(@"SELECT 
        TABLE_CATALOG AS [Database],
        TABLE_SCHEMA AS Owner, 
        TABLE_NAME AS TableName, 
        COLUMN_NAME AS ColumnName, 
        ORDINAL_POSITION AS OrdinalPosition, 
        COLUMN_DEFAULT AS DefaultSetting, 
        IS_NULLABLE AS IsNullable, DATA_TYPE AS DataType, 
        CHARACTER_MAXIMUM_LENGTH AS MaxLength, 
        DATETIME_PRECISION AS DatePrecision,
        COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') AS IsIdentity,
        COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsComputed') as IsComputed
    FROM  INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME=@tableName
    ORDER BY OrdinalPosition ASC");
            // cmd.Parameters.AddWithValue(":Table_Name", tbl.Name);
            dal.AddInParameter(cmd, "@tableName", DbType.String, tbl.Name);
            return cmd;
        }

        public override DbCommand GetFKCommand(string tableName)
        {
            throw new NotImplementedException();
        }

        public override DbCommand GetPKCommand(string table)
        {
            string sql = @"SELECT KCU.COLUMN_NAME 
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
        JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
        ON KCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME
        WHERE TC.CONSTRAINT_TYPE='PRIMARY KEY'
		AND KCU.TABLE_NAME=@tableName";
            //sql = string.Format(sql, dal.ParamsterPre);

            var cmd =dal.GetSqlStringCommond(sql);
            //cmd.Parameters.AddWithValue(":tableName", table);
            dal.AddInParameter(cmd,"@tableName", DbType.String, table);
            return cmd;
        }
    }
}
